package main
import (
	"fmt"
	"github.com/jmoiron/sqlx"
	_ "github.com/go-sql-driver/mysql"
)
//=====数据库连接=====
//initMySQL 初始化mysql连接
var db *sqlx.DB
func initDB() (err error) {
	dsn := "root:@tcp(127.0.0.1:3306)/gogo?charset=utf8mb4&parseTime=True"
	// 也可以使用MustConnect连接不成功就panic
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	return
}

//一般用一个结构体映射一张表的所有字段
type user struct {
	ID   int 
	Age  int  `db:"age"`
	Name string `db:"name"`
}

//=====数据库进阶=====
//sqlx.In批量查询
// QueryByIDs 根据给定ID查询
func QueryByIDs(ids []int)(users []user, err error){
	// 动态填充id
	query, args, err := sqlx.In("SELECT id, name, age FROM user WHERE id IN (?)", ids)
	if err != nil {
		return
	}
	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = db.Rebind(query)

	err = db.Select(&users, query, args...)
	return
}

//NamedExec批量插入
// BatchInsertUsers3 使用NamedExec实现批量插入
var user2 = []user{
	{Name: "我不是故意的",Age: 48},
	{Name: "我是真的真的",Age: 58},
}
func BatchInsertUsers3(users []user) error {
	_, err := db.NamedExec("INSERT INTO user (name, age) VALUES (:name, :age)", users)
	return err
}


//=====数据库基础=====
//=====数据库查询操作=====
// 查询单条数据示例
func queryRowDemo() {
	sqlStr := "select id, name, age from user where id=?"
	var u user
	err := db.Get(&u, sqlStr, 1)
	if err != nil {
		fmt.Printf("get failed, err:%v\n", err)
		return
	}
	fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Age)
}
// 查询多条数据示例
func queryMultiRowDemo() {
	sqlStr := "select id, name, age from user where id > ?"
	var users []user
	err := db.Select(&users, sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	// fmt.Printf("users:%#v\n", users)
	for index,user := range users{
		fmt.Printf("index:%#v  users:%#v\n", index,user)
	}
}
//=====数据库插入操作=====
// 插入数据
func insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "小王子", 19)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() // 新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}
//=====数据库更新操作=====
// 更新数据
func updateRowDemo() {
	sqlStr := "update user set age=? where id = ?"
	ret, err := db.Exec(sqlStr, 39, 5)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}
//=====数据库删除操作=====
// 删除数据
func deleteRowDemo() {
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr, 5)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}


//=====程序入口=====
func main() {
	if err:=initDB();err!=nil {
		fmt.Println("initDB err",err)
		return
	}
	fmt.Println("连接数据库成功！")
	defer db.Close()  //程序退出之前关闭数据库连接

	//=====数据库进阶=====
	//批量查询
	userList,err := QueryByIDs([]int{1,2,3})
	for _,user := range userList {
		fmt.Printf("%#v\n", user)
	}
	fmt.Println("err::",err)
	//批量插入
	BatchInsertUsers3(user2)

	//=====数据库基础=====
	queryRowDemo()
	queryMultiRowDemo()
	insertRowDemo()
	updateRowDemo()
	deleteRowDemo()
	//写在了main下边
	insertUserDemo()
	namedQuery()
	
 }
//NamedExec
 func insertUserDemo()(err error){
	sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"
	_, err = db.NamedExec(sqlStr,
		map[string]interface{}{
			"name": "北大荒",
			"age": 28,
		})
	return
}
//NamedQuery
func namedQuery(){
	sqlStr := "SELECT * FROM user WHERE name=:name"
	// 使用map命名查询
	// rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "北大荒"})
	// if err != nil {
	// 	fmt.Printf("db.NamedQuery failed, err:%v\n", err)
	// 	return
	// }
	// defer rows.Close()
	// for rows.Next(){
	// 	var u user
	// 	err := rows.StructScan(&u)
	// 	if err != nil {
	// 		fmt.Printf("scan failed, err:%v\n", err)
	// 		continue
	// 	}
	// 	fmt.Printf("user:%#v\n", u)
	// }

	u := user{
		Name: "北大荒",
	}
	// 使用结构体命名查询，根据结构体字段的 db tag进行映射
	rows, err := db.NamedQuery(sqlStr, u)
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next(){
		var u user
		err := rows.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}
}
